﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace WaveMaster.Holidays.Club.SQL
{
    public class TravelAgentSQL
    {
        public static string GetAllTravelAgent()
        {
            string sql = "select * from travel_agent ORDER BY m_name aSC";
            return sql;
        }

        public static string GetMaxTravelAgentID()
        {
            string sql = "select max(m_travelAgentId) from travel_agent";
            return sql;
        }

        public static string GetAllActiveTravelAgent()
        {
            string sql = "select * from travel_agent where m_active = 'true' and m_expiredDate >= ?todaydate";
            return sql;
        }


        public static string GetTravelAgentByID()
        {
            string sql = "select * from travel_agent where m_travelAgentId = ?m_travelAgentId ";
            return sql;
        }

        public static string ReportTravelAgent()
        {
            string sql = "select * from travel_agent order by m_code asc";
            return sql;
        }

        public static string SearchTravelAgentByCodeName(string p)
        {
            string sql = "select * from travel_agent WHERE concat(m_code,m_name,m_address) like '%" + p + "%'";
            return sql;
        }
        public static string SearchActiveTravelAgentByCodeName(string p)
        {
            string sql = "select * from travel_agent WHERE concat(m_code,m_name,m_address) like '%" + p + "%' and m_active = 'True'";
            return sql;
        }
        public static string DeleteTravelAgentByID()
        {
            return @"DELETE FROM travel_agent
      WHERE m_travelAgentId = ?travelAgentId";
        }
        public static string InsertTravelAgent()
        {
            string sql = @"INSERT INTO travel_agent
           (m_code
           ,m_name
           ,m_address
           ,m_zipcode
           ,m_contactPerson
           ,m_phone
           ,m_fax
           ,m_email
           ,m_website
           ,m_active
           ,m_cretnDate            
            ,m_cretnBy
            ,m_modfctnDate
            ,m_modfctnBy
            ,m_memberType
,m_registrationDate
,m_expiredDate
,m_regNo
,m_firstName
,m_lastName
,m_gender
,m_mobilePhone
,m_memberEmail
,m_member_type_id
            )
     VALUES
           (?m_code
           ,?m_name
           ,?m_address
           ,?m_zipcode
           ,?m_contactPerson
           ,?m_phone
           ,?m_fax
           ,?m_email
           ,?m_website
           ,?m_active
           ,?m_cretnDate
            , ?m_cretnBy
            , ?m_modfctnDate
            ,?m_modfctnBy
            ,?m_memberType
,?m_registrationDate
,?m_expiredDate
,?m_regNo
,?m_firstName
,?m_lastName
,?m_gender
,?m_mobilePhone
,?m_memberEmail
,?m_member_type_id
            )";
            return sql;
        }
        public static string UpdateTravelAgent()
        {
            string sql = @"UPDATE travel_agent
   SET m_code = ?m_code
           ,m_name=?m_name
           ,m_address=?m_address
           ,m_zipcode=?m_zipcode
           ,m_contactPerson=?m_contactPerson
           ,m_phone=?m_phone
           ,m_fax=?m_fax
           ,m_email=?m_email
           ,m_website=?m_website
           ,m_active=?m_active
            ,m_modfctnDate=?m_modfctnDate
            ,m_modfctnBy=?m_modfctnBy,
            m_memberType=?m_memberType
            ,m_registrationDate =?m_registrationDate
            ,m_expiredDate=?m_expiredDate
,m_regNo=?m_regNo
,m_firstName=?m_firstName
,m_lastName=?m_lastName
,m_gender=?m_gender
,m_mobilePhone=?m_mobilePhone
,m_memberEmail=?m_memberEmail
,m_member_type_id=?m_member_type_id
 WHERE m_travelAgentId = ?m_travelAgentId";
            return sql;
        }

        public static string ReportTravelAgentSummary()
        {
            return @"
                   SELECT
b.*,
(select sum(m_quantity) from ticket_purchase tp where tp.m_travelAgentId =b.m_travelAgentId) as totalPurchase,
(select sum(m_bookNumber) from booking tp where tp.m_travelAgentId = b.m_travelAgentId) as totalBook,
ta.*,
(select tp.m_code from ticket_purchase tp where tp.m_travelAgentId = b.m_travelAgentId
order by m_transactionDate desc limit 1) as lastpurchase,
(select tp.m_bookingCode from booking tp where tp.m_travelAgentId = b.m_travelAgentId
order by m_bookingDate desc limit 1) as lastbooking
FROM book_stock b, travel_agent ta
where b.m_travelAgentId = ta.m_travelAgentId 
                    ";
        }

        public static string ReportByTravelAgent()
        {
            return @"SELECT * FROM travel_agent t, member_type m
where t.m_member_type_id = m.m_member_type_id and m_travelAgentId=?m_travelAgentId";
        }
    }
}
